package com.aidong.dao.wills;

import com.aidong.dao.base.ConditionMap;
import com.aidong.dao.base.Pagination;
import com.aidong.model.wills.*;
import org.hibernate.SQLQuery;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**
 * Created by john on 16-11-3.
 */
@Repository
public class MemberContractDao extends BaseDao<MemberContract, Integer> {
    public MemberContractDao() {
    }

    public MemberContract getByContractCode(String contractCode) {
        return (MemberContract) this.getByWhere("contractCode=:p1", new Object[]{contractCode});
    }

    public MemberContract getByMemberCode(String memberCode) {
        String sql = "select c.* from MemberContract c inner join ContractMember m on c.contractCode=m.contractCode\nwhere m.memberCode=:p1 and effectiveDate is null order by endTime desc";
        List list = this.queryListBySql(sql, new Object[]{memberCode});
        return list.size() > 0 ? (MemberContract) list.get(0) : null;
    }

    public MemberContract getCurrentByMemberCode(String memberCode) {
        String sql = "select c.* from MemberContract c inner join ContractMember m on c.contractCode=m.contractCode\nwhere m.memberCode=:p1 and status=1 and beginTime<getdate() and (endTime>=convert(varchar(10), getdate(),120) or newEndTime>=convert(varchar(10), getdate(),120)) and (effectiveDate is null or effectiveDate>getdate()) order by saleDate desc";
        List list = this.queryListBySql(sql, new Object[]{memberCode});
        return list.size() > 0 ? (MemberContract) list.get(0) : null;
    }

    public List<MemberContract> getCurrentListByMemberCode(String memberCode) {
        String sql = "select c.* from MemberContract c inner join ContractMember m on c.contractCode=m.contractCode\nwhere m.memberCode=:p1 and status=1 and (endTime>=convert(varchar(10), getdate(),120) or newEndTime>=convert(varchar(10), getdate(),120)) and (effectiveDate is null or effectiveDate>getdate()) order by endTime desc";
        return this.queryListBySql(sql, new Object[]{memberCode});
    }

    public Date getMaxEndTimeByMemberCode(String memberCode) {
        String sql = "select max(endTime) from MemberContract c inner join ContractMember m on c.contractCode=m.contractCode inner join CardTypeCode ct on c.cardType=ct.code\nwhere m.memberCode=:p1 and saleType<>\'8\' and directorCard<>1 and effectiveDate is null";
        return (Date) this.getBySql(Date.class, sql, new Object[]{memberCode});
    }

    public List<MemberContract> getEnterListByMemberCode(String memberCode, Date date) {
        String sql = "select c.* from MemberContract c inner join ContractMember m on c.contractCode=m.contractCode inner join CardTypeCode ct on c.cardType=ct.code\nwhere m.memberCode=:p1 and c.status=1 and directorCard<>1 and beginTime<:p2 and (endTime>=convert(varchar(10),:p3,120) or newEndTime>=convert(varchar(10),:p4,120)) and (effectiveDate is null or effectiveDate>:p5) order by endTime desc";
        return this.queryListBySql(sql, new Object[]{memberCode, date, date, date, date});
    }

    public MemberContract getStopedByMemberCode(String memberCode) {
        String sql = "select c.* from MemberContract c inner join ContractMember m on c.contractCode=m.contractCode\ninner join MemberStopedCard s on c.contractCode=s.contractCode\nwhere m.memberCode=:p1 and c.effectiveType=\'6\' and s.status=1";
        List list = this.queryListBySql(sql, new Object[]{memberCode});
        return list.size() > 0 ? (MemberContract) list.get(0) : null;
    }

    public MemberContract getNotOpenByMemberCode(String memberCode) {
        String sql = "select c.* from MemberContract c inner join ContractMember m on c.contractCode=m.contractCode\nwhere m.memberCode=:p1 and beginTime>getdate() and (endTime>=convert(varchar(10), getdate(),120) or newEndTime>=convert(varchar(10), getdate(),120)) and effectiveDate is null order by beginTime";
        List list = this.queryListBySql(sql, new Object[]{memberCode});
        return list.size() > 0 ? (MemberContract) list.get(0) : null;
    }

    public List<String> getMemberCodeByContractCode(String contractCode) {
        String sql = "select memberCode from ContractMember where contractCode=:p1";
        return this.queryListBySql(String.class, sql, new Object[]{contractCode});
    }

    public List<String> getShopCodeByContractCode(String contractCode) {
        String sql = "select shopCode from ContractShop where contractCode=:p1";
        return this.queryListBySql(String.class, sql, new Object[]{contractCode});
    }

    public Date getFirstDateByMemberCode(String memberCode) {
        String sql = "select top 1 saleDate from MemberContract c inner join ContractMember m on c.contractCode=m.contractCode\nwhere m.memberCode=:p1 order by saleDate";
        return (Date) this.getBySql(Date.class, sql, new Object[]{memberCode});
    }

    public boolean chkContractShop(String contractCode, String shopCode) {
        String sql = "select count(*) from ContractShop where contractCode=:p1 and shopCode=:p2";
        return ((Integer) this.getBySql(Integer.class, sql, new Object[]{contractCode, shopCode})).intValue() > 0;
    }

    public int getMobileVerifyCount(String mobile) {
        String sql = "select count(*) from MemberContract where verifyMobile=:p1 and verifyStatus in (1,3) and saleDate>getdate()-365";
        return ((Integer) this.getBySql(Integer.class, sql, new Object[]{mobile})).intValue();
    }

    public List<MemberContract> getCardPriceList(Date dateFrom, Date dateTo) {
        String sql = "select shopCode,sum(totalPrice) totalPrice,sum(inAmount+earnestAmount+payAmount) factPrice, category cardType, c.valids\nfrom MemberContract c inner join CardTypeCode ct on c.cardType=ct.code\nwhere saleType in (\'1\',\'2\',\'3\') and typeId=\'01\' and category in (\'01\',\'02\',\'03\',\'04\') and c.validUnit=\'04\'\nand saleDate>=:p1 and saleDate<=:p2\ngroup by shopCode, category, c.valids\norder by shopCode, cardType, valids";
        return this.queryListBySql(MemberContract.class, sql, new Object[]{dateFrom, dateTo});
    }

    public List<MemberContract> getListByMemberCode(String memberCode) {
        String sql = "select * from MemberContract where memberCode=:p1\nunion select c.* from MemberContract c inner join ContractMember m on c.contractCode=m.contractCode\nwhere m.memberCode=:p2 order by saleDate";
        return this.queryListBySql(sql, new Object[]{memberCode, memberCode});
    }

    public void queryPageList(Pagination<ContractInfo> pg, ConditionMap cd) {
        ArrayList params = new ArrayList();
        String sqlw = cd.toSql(params);
        String sql = "select c.saleId,c.shopCode,c.contractCode,c.saleType,c.saleDate,c.beginTime,c.endTime,c.newEndTime,c.cardType,c.memberCode,c.cardCode,m.chName memberName, c.totalCount, c.restCount\n,c.cardPrice,c.totalPrice,c.factPrice,c.earnestAmount,c.inAmount,c.payAmount,c.status,c.commissionDate,c.salesCode,c.salesCode2,c.createUser,c.note,c.receivePaper,c.reviewStatus,c.reviewDate\n,c.effectiveDate,c.effectiveType,c.outAmount,c.earnestId,c.giftCardType,c.giftCardCode,c.giftCardAmount\nfrom MemberContract c inner join MemberInfo m on c.memberCode=m.memberCode\n" + (sqlw.length() > 0 ? " where " + sqlw : "") + " order by c.saleId desc";
        this.queryPageListBySql(ContractInfo.class, pg, sql, params.toArray());
    }

    public void queryPageList2(Pagination<ContractInfo> pg, ConditionMap cd) {
        ArrayList params = new ArrayList();
        String sqlw = cd.toSql(params);
        String sql = "select c.saleId,c.shopCode,c.contractCode,c.saleType,c.saleDate,c.beginTime,c.endTime,c.newEndTime,c.cardType,m.memberCode,m.cardCode,m.chName memberName, c.totalCount, c.restCount\n,c.cardPrice,c.totalPrice,c.factPrice,c.earnestAmount,c.inAmount,c.payAmount,c.status,c.commissionDate,c.salesCode,c.salesCode2,c.createUser,c.note,c.receivePaper,c.reviewStatus,c.reviewDate\n,c.effectiveDate,c.effectiveType,c.outAmount,c.earnestId\nfrom MemberContract c inner join ContractMember cm on c.contractCode=cm.contractCode\ninner join MemberInfo m on cm.memberCode=m.memberCode\n" + (sqlw.length() > 0 ? " where " + sqlw : "") + " order by c.saleId desc";
        this.queryPageListBySql(ContractInfo.class, pg, sql, params.toArray());
    }

    public void queryPageExportList(Pagination<ContractInfo> pg, ConditionMap cd) {
        ArrayList params = new ArrayList();
        String sqlw = cd.toSql(params);
        String sql = "select c.saleId,c.shopCode,c.contractCode,c.saleType,c.saleDate,c.beginTime,c.endTime,c.newEndTime,c.cardType,c.memberCode,c.cardCode,m.chName memberName\n,c.cardPrice,c.totalPrice,c.factPrice,c.earnestAmount,c.inAmount,c.payAmount,c.status,c.commissionDate,c.salesCode,c.salesCode2,c.createUser,c.note,c.receivePaper,c.reviewStatus,c.reviewDate\n,c.effectiveDate,c.effectiveType,c.outAmount,c.earnestId,e.saleDate earnestSaleDate,c.oldContractCode,c2.saleDate oldSaleDate,c2.cardType oldCardType,c2.factPrice oldFactPrice, e.earnestDelay\nfrom MemberContract c inner join MemberInfo m on c.memberCode=m.memberCode\nleft join MemberEarnest e on c.earnestId=e.saleId\nleft join MemberContract c2 on c.oldContractCode=c2.contractCode\n" + (sqlw.length() > 0 ? " where " + sqlw : "") + " order by c.shopCode, c.saleDate";
        this.queryPageListBySql(ContractInfo.class, pg, sql, params.toArray());
    }

    public void queryPagePrintList(Pagination<ContractInfo> pg, ConditionMap cd) {
        ArrayList params = new ArrayList();
        String sqlw = cd.toSql(params);
        String sql = "select mc.saleId,mc.contractCode,mc.shopCode,saleType,saleDate,beginTime,endTime,cardType,payAmount,mc.memberCode,mi.cardCode,mi.chName memberName, cs.status\nfrom MemberContract mc inner join ContractSign cs on mc.saleId=cs.saleId and cs.contractType=\'01\'\ninner join MemberInfo mi on mc.memberCode=mi.memberCode\nwhere mc.status=1 and (mc.endTime>getdate() or mc.newEndTime>getdate()) and mc.effectiveDate is null" + (sqlw.length() > 0 ? " and " + sqlw : "") + " order by mc.saleId desc";
        this.queryPageListBySql(ContractInfo.class, pg, sql, params.toArray());
    }

    public boolean updatePrint(MemberContract contract) {
        String sql = "update MemberContract set printLabel=:p1, printTime=:p2, printUser=:p3 where saleId=:p4";
        return this.updateBySql(sql, new Object[]{contract.getPrintLabel(), contract.getPrintTime(), contract.getPrintUser(), contract.getSaleId()}) > 0;
    }

    public boolean updateContractTime(MemberContract contract) {
        String sql = "update MemberContract set beginTime=:p1, endTime=:p2, newEndTime=:p3, note=:p4, updateTime=getdate() where saleId=:p5";
        return this.updateBySql(sql, new Object[]{contract.getBeginTime(), contract.getEndTime(), contract.getNewEndTime(), contract.getNote(), contract.getSaleId()}) > 0;
    }

    public boolean updateVerifyStatus(int saleId, int status) {
        String sql = "update MemberContract set verifyStatus=:p1 where saleId=:p2";
        return this.updateBySql(sql, new Object[]{Integer.valueOf(status), Integer.valueOf(saleId)}) > 0;
    }

    public void addContract(MemberContract contract, List<String> shopList, List<MemberInfo> memberList, List<PayList> payList) {
        String shopCode = contract.getShopCode();
        String sql = "select max(contractCode) from MemberContract where shopCode=:p1 and contractCode like :p2";
        String maxCode = (String) this.getBySql(String.class, sql, new Object[]{shopCode, shopCode + "%"});
        int maxId = 0;
        if (maxCode != null && maxCode.length() > shopCode.length()) {
            try {
                maxId = Integer.parseInt(maxCode.substring(shopCode.length()));
            } catch (Exception var18) {
                ;
            }
        }

        String contractCode = "000000" + (maxId + 1);
        contractCode = shopCode + contractCode.substring(contractCode.length() - 6);
        SimpleDateFormat sdf = new SimpleDateFormat("yyMMdd");
        String memberPrefix = shopCode + sdf.format(new Date());
        sql = "select max(memberCode) from MemberInfo where shopCode=:p1 and memberCode like :p2";
        maxCode = (String) this.getBySql(String.class, sql, new Object[]{shopCode, memberPrefix + "%"});
        maxId = 0;
        if (maxCode != null && maxCode.length() > shopCode.length()) {
            try {
                maxId = Integer.parseInt(maxCode.substring(memberPrefix.length()));
            } catch (Exception var17) {
                ;
            }
        }

        ++maxId;
        SQLQuery query = this.getSession().createSQLQuery("insert into ContractShop (ContractCode, ShopCode) values (:p1, :p2)");
        query.setString("p1", contractCode);
        Iterator member0 = shopList.iterator();

        while (member0.hasNext()) {
            String sign = (String) member0.next();
            query.setString("p2", sign);
            query.executeUpdate();
        }

        member0 = memberList.iterator();

        while (true) {
            MemberInfo var20;
            while (member0.hasNext()) {
                var20 = (MemberInfo) member0.next();
                String memberCode = var20.getMemberCode();
                if (memberCode == null || memberCode.length() == 0) {
                    memberCode = "0000" + maxId++;
                    memberCode = memberPrefix + memberCode.substring(memberCode.length() - 4);
                }

                if (var20.getStatus().intValue() == 1) {
                    sql = "update MemberCard set memberCode=:p1, contractCode=:p2, status=2, useUser=:p3, useDate=getdate() where cardCode=:p4";
                    this.updateBySql(sql, new Object[]{memberCode, contractCode, contract.getCreateUser(), var20.getCardCode()});
                }

                if (var20.getMemberCode() != null && var20.getMemberCode().length() != 0) {
                    if (var20.getStatus().intValue() == 1) {
                        sql = "update MemberInfo set cardCode=:p1, memberPhoto=:p2, idCardPhoto=:p3, updateTime=getdate() where memberCode=:p4";
                        this.updateBySql(sql, new Object[]{var20.getCardCode(), var20.getMemberPhoto(), var20.getIdCardPhoto(), memberCode});
                    } else {
                        sql = "update MemberInfo set memberPhoto=:p1, idCardPhoto=:p2, updateTime=getdate() where memberCode=:p3";
                        this.updateBySql(sql, new Object[]{var20.getMemberPhoto(), var20.getIdCardPhoto(), memberCode});
                    }
                } else {
                    var20.setMemberCode(memberCode);
                    this.getSession().save(var20);
                }
            }

            query = this.getSession().createSQLQuery("insert into ContractMember (ContractCode, MemberCode) values (:p1, :p2)");
            query.setString("p1", contractCode);
            member0 = memberList.iterator();

            while (member0.hasNext()) {
                var20 = (MemberInfo) member0.next();
                query.setString("p2", var20.getMemberCode());
                query.executeUpdate();
            }

            MemberInfo var19 = null;
            Iterator var21 = memberList.iterator();

            while (var21.hasNext()) {
                MemberInfo detail04 = (MemberInfo) var21.next();
                if (detail04.getCardCode().equalsIgnoreCase(contract.getCardCode())) {
                    contract.setMemberCode(detail04.getMemberCode());
                    var19 = detail04;
                }
            }

            if (contract.getMemberCode() == null && memberList.size() > 0) {
                contract.setCardCode(((MemberInfo) memberList.get(0)).getCardCode());
                contract.setMemberCode(((MemberInfo) memberList.get(0)).getMemberCode());
                var19 = (MemberInfo) memberList.get(0);
            }

            contract.setContractCode(contractCode);
            this.add(contract);
            var21 = payList.iterator();

            while (var21.hasNext()) {
                PayList var22 = (PayList) var21.next();
                var22.setSaleId(contract.getSaleId());
                var22.setContractCode(contractCode);
                this.getSession().save(var22);
                if ("S011".equalsIgnoreCase(var22.getPayType())) {
                    sql = "update PayAli set status=:p1, payListId=:p2, salesCode=:p3, salesCode2=:p4, updateTime=:p5 where barCode=:p6";
                    this.updateBySql(sql, new Object[]{Integer.valueOf(2), var22.getAutoId(), var22.getSalesCode(), var22.getSalesCode2(), new Date(), var22.getNote()});
                }

                if ("S016".equalsIgnoreCase(var22.getPayType())) {
                    sql = "update PayBaosteel set status=:p1, payListId=:p2, updateTime=:p3 where autoId=:p4";
                    this.updateBySql(sql, new Object[]{Integer.valueOf(2), var22.getAutoId(), new Date(), Integer.valueOf(Integer.parseInt(var22.getNote()))});
                }
            }

            if ("2".equals(contract.getSaleType()) && contract.getOldContractCode() != null && contract.getOldContractCode().length() > 0) {
                sql = "update MemberContract set effectiveDate=:p1, effectiveType=:p2, outAmount=:p3, updateTime=:p4 where contractCode=:p5";
                this.updateBySql(sql, new Object[]{new Date(), "2", contract.getInAmount(), new Date(), contract.getOldContractCode()});
            }

            if (contract.getEarnestId() != null) {
                sql = "update MemberEarnest set status=1, useSaleId=:p1, useDate=getdate() where saleId=:p2";
                this.updateBySql(sql, new Object[]{contract.getSaleId(), contract.getEarnestId()});
            }

            if (contract.getGiftCardCode() != null && contract.getGiftCardCode().length() > 0) {
                sql = "update GiftCard set status=2, receiveUser=:p1, receiveDate=getdate() where cardCode=:p2";
                this.updateBySql(sql, new Object[]{contract.getCreateUser(), contract.getGiftCardCode()});
            }

            if (contract.getStatus().intValue() == 2) {
                WorkflowList var23 = new WorkflowList();
                var23.setShopCode(contract.getShopCode());
                var23.setTypeId("04");
                var23.setContractCode(contract.getContractCode());
                var23.setMemberCode(contract.getMemberCode());
                var23.setMemberName(var19 == null ? "" : var19.getChName());
                var23.setFileNum(Integer.valueOf(0));
                var23.setStatus(Integer.valueOf(0));
                var23.setProcResult1(Integer.valueOf(0));
                var23.setProcResult2(Integer.valueOf(0));
                var23.setProcResult3(Integer.valueOf(0));
                var23.setCreateUser(contract.getCreateUser());
                var23.setCreateTime(new Date());
                var23.setUpdateTime(new Date());
                this.getSession().save(var23);
                WorkflowDetail04 var24 = new WorkflowDetail04();
                var24.setWorkflowId(var23.getWorkflowId());
                var24.setContractCode(contract.getContractCode());
                this.getSession().save(var24);
            }

            ContractSign var25 = new ContractSign();
            var25.setShopCode(contract.getShopCode());
            var25.setContractCode(contract.getContractCode());
            var25.setContractType("01");
            var25.setSaleId(contract.getSaleId());
            var25.setMemberCode(contract.getMemberCode());
            var25.setMemberName(var19 == null ? "" : var19.getChName());
            var25.setStatus(Integer.valueOf(contract.getStatus().intValue() == 2 ? 0 : 1));
            var25.setSalesCode(contract.getSalesCode());
            var25.setSalesCode2(contract.getSalesCode2());
            var25.setCreateUser(contract.getCreateUser());
            var25.setCreateTime(new Date());
            var25.setUpdateTime(new Date());
            this.getSession().save(var25);
            return;
        }
    }

    public void addContractApprove(MemberContract contract) {
        String sql = "select workflowId from WorkflowList where typeId=:p1 and contractCode=:p2";
        Integer workflowId = (Integer) this.getBySql(Integer.class, sql, new Object[]{"04", contract.getContractCode()});
        if (workflowId != null) {
            sql = "update WorkflowList set status=1, submitTime=getdate(), procResult1=0, procNote1=null, procUser1=null, procTime1=null, procResult2=0, procNote2=null, procUser2=null, procTime2=null, procResult3=0, procNote3=null, procUser3=null, procTime3=null, updateTime=getdate() where workflowId=:p1";
            this.updateBySql(sql, new Object[]{workflowId});
        } else {
            String memberName = (String) this.getBySql(String.class, "select chName from MemberInfo where memberCode=:p1", new Object[]{contract.getMemberCode()});
            WorkflowList workflow = new WorkflowList();
            workflow.setShopCode(contract.getShopCode());
            workflow.setTypeId("04");
            workflow.setContractCode(contract.getContractCode());
            workflow.setMemberCode(contract.getMemberCode());
            workflow.setMemberName(memberName == null ? "" : memberName);
            workflow.setFileNum(Integer.valueOf(0));
            workflow.setStatus(Integer.valueOf(1));
            workflow.setSubmitTime(new Date());
            workflow.setProcResult1(Integer.valueOf(0));
            workflow.setProcResult2(Integer.valueOf(0));
            workflow.setProcResult3(Integer.valueOf(0));
            workflow.setCreateUser(contract.getCreateUser());
            workflow.setCreateTime(new Date());
            workflow.setUpdateTime(new Date());
            this.getSession().save(workflow);
            WorkflowDetail04 detail04 = new WorkflowDetail04();
            detail04.setWorkflowId(workflow.getWorkflowId());
            detail04.setContractCode(contract.getContractCode());
            this.getSession().save(detail04);
        }

    }

    public void addContractSign(MemberContract contract) {
        String sql = "select autoId from ContractSign where contractType=:p1 and contractCode=:p2";
        Integer id = (Integer) this.getBySql(Integer.class, sql, new Object[]{"01", contract.getContractCode()});
        if (id == null) {
            String memberName = (String) this.getBySql(String.class, "select chName from MemberInfo where memberCode=:p1", new Object[]{contract.getMemberCode()});
            ContractSign sign = new ContractSign();
            sign.setShopCode(contract.getShopCode());
            sign.setContractCode(contract.getContractCode());
            sign.setContractType("01");
            sign.setSaleId(contract.getSaleId());
            sign.setMemberCode(contract.getMemberCode());
            sign.setMemberName(memberName == null ? "" : memberName);
            sign.setStatus(Integer.valueOf(contract.getStatus().intValue() == 2 ? 0 : 1));
            sign.setSalesCode(contract.getSalesCode());
            sign.setSalesCode2(contract.getSalesCode2());
            sign.setCreateUser(contract.getCreateUser());
            sign.setCreateTime(new Date());
            sign.setUpdateTime(new Date());
            this.getSession().save(sign);
            sql = "update MemberContract set SignStatus=2,PrintLabel=0 where saleId=:p1";
            this.updateBySql(sql, new Object[]{contract.getSaleId()});
        }

    }
}
